package com.maserb.monitor.repository.impl;

import com.maserb.monitor.common.IdUtils;
import com.maserb.monitor.dataCenter.communication.EigenWarningStatusEnum;
import com.maserb.monitor.dataCenter.model.EigenValueResponseResult;
import com.maserb.monitor.dataCenter.model.HistoryEigenRequest;
import com.maserb.monitor.dataCenter.model.HistoryEigenResponseResult;
import com.maserb.monitor.database.JdbcTemplateFactory;
import com.maserb.monitor.database.PreparedStatementUtils;
import com.maserb.monitor.entity.EigenValueEntity;
import com.maserb.monitor.repository.EigenValueRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

@Repository
public class EigenValueRepositoryImpl implements EigenValueRepository {
    @Autowired
    JdbcTemplateFactory jdbcTemplateFactory;

    @Transactional
    @Override
    public void save(EigenValueEntity entity, String deviceCode) {
        JdbcTemplate jdbcTemplate = jdbcTemplateFactory.getTemplate(deviceCode);
        String tableName = getTablename(Calendar.getInstance().get(Calendar.YEAR));
        makesureTableExists(jdbcTemplate, tableName);
        entity.setId(IdUtils.generateId());
        String insertSql = "insert into " + tableName + "(pointCode,sampleTime," +
                "value0,value1,value2,value3,value4,value5,value6,value7,value8," +
                "deviceCode," +
                "id) " +
                "values(?,?, ?,?,?,?,?,?,?,?,?, ?,?)";
        prepareSaveParameters(entity, jdbcTemplate, insertSql);
    }

    @Override
    public HistoryEigenResponseResult getHistoryData(HistoryEigenRequest request) throws Exception {
        JdbcTemplate jdbcTemplate = jdbcTemplateFactory.getTemplate(request.getDeviceCode());
        HistoryEigenResponseResult result = new HistoryEigenResponseResult();
        result.setData(new LinkedList<>());
        Calendar maxCalendar = Calendar.getInstance();
        maxCalendar.setTime(request.getEndTime());

        Calendar nowCalendar = Calendar.getInstance();
        nowCalendar.setTime(new Date());
        if (maxCalendar.compareTo(nowCalendar) > 0) {
            maxCalendar = nowCalendar;
        }

        Calendar minCalendar = Calendar.getInstance();
        minCalendar.setTime(request.getStartTime());

        if (maxCalendar.compareTo(minCalendar) < 0) {
            throw new Exception("request time error");
        }

        for (int i = maxCalendar.get(Calendar.YEAR); i > minCalendar.get(Calendar.YEAR); i--) {
            String tableName = getTablename(i);
            if (!checkTableExists(jdbcTemplate, tableName)) {
                break;
            }
            String valueX = "value" + request.getEigenCode();
            String statusX = "status" + request.getEigenCode();
            String sql = "select sampleTime," + valueX +
                    "," + statusX + " from " + tableName + " where pointCode = ? order by sampleTime asc";
            jdbcTemplate.query(sql, new Object[]{request.getPointCode()}, new RowCallbackHandler() {
                @Override
                public void processRow(ResultSet rs) throws SQLException {
                    EigenValueResponseResult r = new EigenValueResponseResult();
                    r.setSampleTime(rs.getString(1));
                    r.setName(rs.getString(1));
                    r.setEigenValue(rs.getFloat(2));
                    r.setValue(new Object[]{r.getSampleTime(), r.getEigenValue()});
                    r.setStatus(EigenWarningStatusEnum.getIndex(rs.getInt(3)));
                    result.getData().add(r);
                }
            });
        }

        return result;
    }

    private void prepareSaveParameters(EigenValueEntity entity, JdbcTemplate jdbcTemplate, String insertSql) {
        jdbcTemplate.update(insertSql, ps -> {
            PreparedStatementUtils.setString(ps, 1, entity.getPointCode());
            PreparedStatementUtils.setDate(ps, 2, entity.getSampleTime());
            PreparedStatementUtils.setString(ps, 3, entity.getValue0());
            PreparedStatementUtils.setString(ps, 4, entity.getValue1());
            PreparedStatementUtils.setString(ps, 5, entity.getValue2());
            PreparedStatementUtils.setString(ps, 6, entity.getValue3());
            PreparedStatementUtils.setString(ps, 7, entity.getValue4());
            PreparedStatementUtils.setString(ps, 8, entity.getValue5());
            PreparedStatementUtils.setString(ps, 9, entity.getValue6());
            PreparedStatementUtils.setString(ps, 10, entity.getValue7());
            PreparedStatementUtils.setString(ps, 11, entity.getValue8());
            PreparedStatementUtils.setString(ps, 12, entity.getDeviceCode());
            PreparedStatementUtils.setString(ps, 13, entity.getId());
        });
    }

    private void makesureTableExists(JdbcTemplate jdbcTemplate, String tableName) {
        String existTableSql = "SELECT COUNT(*) FROM information_schema.\n" +
                "TABLES WHERE TABLE_NAME='" + tableName + "'";

        Integer count = jdbcTemplate.queryForObject(existTableSql, new Object[]{}, Integer.class);
        if (count < 1) {
            String createTableSql = getCreateTableSql(tableName);
            jdbcTemplate.execute(createTableSql);
        }
    }

    private Boolean checkTableExists(JdbcTemplate jdbcTemplate, String tableName) {
        String existTableSql = "SELECT COUNT(*) FROM information_schema.\n" +
                "TABLES WHERE TABLE_NAME='" + tableName + "'";

        Integer count = jdbcTemplate.queryForObject(existTableSql, new Object[]{}, Integer.class);
        return count > 0;
    }

    private String getCreateTableSql(String tableName) {
        return "CREATE TABLE `" + tableName + "` (\n" +
                "  `id` char(32) NOT NULL,\n" +
                "  `pointCode` char(32) DEFAULT NULL,\n" +
                "  `deviceCode` char(32) DEFAULT NULL,\n" +
                "  `sampleTime` datetime DEFAULT NULL,\n" +
                "  `value0` varchar(100) DEFAULT NULL,\n" +
                "  `value1` varchar(100) DEFAULT NULL,\n" +
                "  `value2` varchar(100) DEFAULT NULL,\n" +
                "  `value3` varchar(100) DEFAULT NULL,\n" +
                "  `value4` varchar(100) DEFAULT NULL,\n" +
                "  `value5` varchar(100) DEFAULT NULL,\n" +
                "  `value6` varchar(100) DEFAULT NULL,\n" +
                "  `value7` varchar(100) DEFAULT NULL,\n" +
                "  `value8` varchar(100) DEFAULT NULL,\n" +
                "  PRIMARY KEY (`id`)\n" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    }

    private String getTablename(int year) {
        String tableName = "eigen_value_" + year;
        return tableName;
    }
}
